package demo.utils;

/**
 * @author : GuoFei
 * @date : 2018/10/10 17:07
 */

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;

public class XLSUtil {

    static Logger logger = LoggerFactory.getLogger("xls");

    public static void main(String[] args)  {
        System.out.println(JSON.toJSONString(getExcelInfo("C:\\Users\\mayn\\Desktop\\2019-03-06模板推送话术修改.xls")));
    }

    public static HashMap<String, HashMap<String, JSONArray>> getExcelInfo(String path) {
        // 1. 获取最大行数，最大列数，每个单元格文案
        Workbook workbook = readXls(path);
        int sheetNum = workbook.getNumberOfSheets();
        HashMap<String, HashMap<String, JSONArray>> allMap = new HashMap<>();
        for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++) {
            Sheet sheet = workbook.getSheetAt(sheetIndex);
            if (sheet == null) {
                throw new RuntimeException("获取到第{" + (sheetIndex + 1) + "}个工作簿为空");
            }
            logger.info("开始匹配工作表【{}】", sheet.getSheetName());
            allMap.put(sheet.getSheetName(), new HashMap<>());
            int rowNum = sheet.getPhysicalNumberOfRows();
            for (int rowIndex = 0; rowIndex < rowNum; rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    throw new RuntimeException("获取到第【" + (sheetIndex + 1) + "】个工作簿的第【" + (rowIndex + 1) + "】行为空");
                }

                int cellNum = row.getPhysicalNumberOfCells();
                for (int cellIndex = 0; cellIndex < cellNum; cellIndex++) {
                    Cell cell = row.getCell(cellIndex);
                    if (cell == null) {
                        throw new RuntimeException("获取到第【" + (sheetIndex + 1) + "】个工作簿的第【" + (rowIndex + 1) + "】行第【" + (cellIndex) + "】列为空");
                    }
                    if (StringUtils.isNotBlank(cell.getStringCellValue())) {
                        if (rowIndex == 0) {
                            if (!allMap.get(sheet.getSheetName()).containsKey(cell.getStringCellValue())) {
                                allMap.get(sheet.getSheetName()).put(cell.getStringCellValue(), new JSONArray());
                            }
                        } else {
                            Cell keyCell = sheet.getRow(0).getCell(cellIndex);
                            allMap.get(sheet.getSheetName()).get(keyCell.getStringCellValue()).add(cell.getStringCellValue());
                        }
                    }
                }
            }
        }
        return allMap;
    }

    private static String getValue(XSSFCell xssfRow) {
        if (xssfRow.getCellType() == xssfRow.CELL_TYPE_BOOLEAN) {
            return String.valueOf(xssfRow.getBooleanCellValue());
        } else if (xssfRow.getCellType() == xssfRow.CELL_TYPE_NUMERIC) {
            String test = xssfRow.toString();
            Date date = xssfRow.getDateCellValue();
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd HH:mm");
            return sdf.format(date).toString();
//            DecimalFormat df = new DecimalFormat("0");
//            return df.format(xssfRow.getNumericCellValue()).toString();
            //return String.valueOf(xssfRow.getNumericCellValue());
        } else {
            return String.valueOf(xssfRow.getStringCellValue());
        }
    }

    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_NUMERIC:{
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                }
                case Cell.CELL_TYPE_FORMULA:{
                    //判断cell是否为日期格式
                    if(DateUtil.isCellDateFormatted(cell)){
                        //转换为日期格式YYYY-mm-dd
                        cellValue = cell.getDateCellValue();
                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    }
                    break;
                }
                case Cell.CELL_TYPE_STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }

    //读取excel
    public static Workbook readXls(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }

        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    /**
     * @author SHUN
     * @param cell  import org.apache.poi.ss.usermodel.Cell;
     * 关于如何将Excel表格中的时间字符串的数字格式  转换成 格式化的时间字符串
     * @return
     */
    public String excelTime(Cell cell){
        String guarantee_time = null;
        if(DateUtil.isCellDateFormatted(cell)){
            //用于转化为日期格式
            Date d = cell.getDateCellValue();
//	             System.err.println(d.toString());
//	             DateFormat formater = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            DateFormat formater = new SimpleDateFormat("yyyyMMdd HH:mm:ss");
            guarantee_time = formater.format(d);
        }
        return guarantee_time;
    }

    /**
     * 分多种格式解析单元格的值
     *
     * @param cell  单元格
     * @return  单元格的值
     */
    public static String convertCellToString(Cell cell){
        //如果为null会抛出异常，应当返回空字符串
        if (cell == null)
            return "";

        //POI对单元格日期处理很弱，没有针对的类型，日期类型取出来的也是一个double值，所以同样作为数值类型
        //解决日期2006/11/02格式读入后出错的问题，POI读取后变成“02-十一月-2006”格式
        if(cell.toString().contains("-") && checkDate(cell.toString())){
            String ans = "";
            try {
                ans = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
            } catch (Exception e) {
                ans = cell.toString();
            }
            return ans;
        }

//        cell.setCellType(CellType.STRING);
        return cell.getStringCellValue();
    }

    /**
     * 判断是否是“02-十一月-2006”格式的日期类型
     */
    private static boolean checkDate(String str){
        String[] dataArr =str.split("-");
        try {
            if(dataArr.length == 3){
                int x = Integer.parseInt(dataArr[0]);
                String y =  dataArr[1];
                int z = Integer.parseInt(dataArr[2]);
                if(x>0 && x<32 && z>0 && z< 10000 && y.endsWith("月")){
                    return true;
                }
            }
        } catch (Exception e) {
            return false;
        }
        return false;
    }


}
